﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data.Sql;

namespace DAO
{
    public class AccesoDato
    {
        private readonly String cadenaConexion = "Data Source=ANGELO-PC\\SQLEXPRESS;Initial Catalog=bd_pav2;User ID=bd_user;Password=123";
        //private readonly String cadenaConexion = "Data Source=ANGELO-NOTEBOOK\\SQLEXPRESS;Initial Catalog=bd_pav2;User ID=bd_user;Password=123";
        //private readonly String cadenaConexion = "Data Source=Micheletti\\sqlexpress;Initial Catalog=bd_pav2;Integrated Security=True";
        private SqlConnection con;
        private SqlDataReader dr;

        public String getCadena()
        {
            return cadenaConexion;
        }

        public AccesoDato()
        {
            this.con = new SqlConnection(cadenaConexion);
        }

        private void conectar()
        {
            try
            {
                this.con.Open();
            }
            catch (SqlException)
            {
                throw new ApplicationException("Error de SQL");
            }
        }

        private void desconectar()
        {
            try
            {
                if (con.State == System.Data.ConnectionState.Open)
                {
                    this.con.Close();
                }
            }
            catch (Exception)
            {
                throw new ApplicationException("Error de SQL");
            }
        }

        public SqlDataReader ejecutarSql(String sql, List<SqlParameter> parametros)
        {
            try
            {
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = con;
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.CommandText = sql;
                if (parametros != null)
                {
                    foreach (SqlParameter item in parametros)
                    {
                        cmd.Parameters.Add(item);
                    }
                }
                this.conectar();
                return dr = cmd.ExecuteReader();
            }
            catch (SqlException)
            {
                throw new ApplicationException("Error de SQL");
            }
        }

        public void ejecutarNonQuery(String sql, List<SqlParameter> parametros)
        {
            try
            {
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = con;
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.CommandText = sql;
                if (parametros != null)
                {
                    foreach (SqlParameter item in parametros)
                    {
                        cmd.Parameters.Add(item);
                    }
                }
                this.conectar();
                cmd.ExecuteNonQuery();
            }
            catch (SqlException)
            {
                throw new ApplicationException("Error de SQL");
            }
            finally
            {
                this.desconectar();
            }
        }


        public void cerrarDR()
        {
            try
            {
                if (dr != null)
                {
                    this.dr.Close();
                }
                this.desconectar();
            }
            catch (SqlException)
            {

                throw new ApplicationException("Error de SQL");
            }
        }

    }
}
